<?php
!defined('IN_ROOT') && exit( 'Access Denied' );
class ReportController	extends	AdminModuleController {
	
	public function actionProduct() {
		//地区分类
		$provinceList = $this->AreaModel->findAll("fId='1'", 0, 0, "id ASC");
		
		$page = getgpc('page');
		
		if(isset($_POST['submitTo']) || is_numeric($page)) {
			$sqladd = '';
			$startDate = getgpc('startDate', 'R');
			$endDate = getgpc('endDate', 'R');
			if($startDate && $endDate) {
				$startDate = str_replace('-', '', $startDate);
				$endDate = str_replace('-', '', $endDate);
				$sqladd .= " (reportDate>='$startDate' AND reportDate<='$endDate') AND";
			}
			
			$province = getgpc('province', 'R');
			if($province) {
				$sqladd .= " province='$province' AND";
			}
			$city = getgpc('city', 'R');
			if($city) {
				$sqladd .= " city='$city' AND";
			}
			if($sqladd) {
				$sqladd = "WHERE ".substr($sqladd, 0, -3);
			}
			
			$page = max(1, $page);
			$ppp = GODHOUSE_PPP3;
			$start = ($page-1)*$ppp;

			// 产品销量排行
			$sql = "SELECT SUM(cartCount) as num, productId FROM god_report_basis $sqladd GROUP BY productId ORDER BY num DESC LIMIT $start, $ppp";
			//echo $sqladd;exit;
			$reportList = $this->db->fetch_all($sql);
			foreach($reportList as $k=>&$v) {
				$product = $this->ProductModel->findByPk($v['productId']);
				$category = $this->CategoryModel->findByPk($product['categoryId']);
				$v['cateName'] = $category['cateName'];
				$v['content'] = $product['content'];
				$v['productNo'] = $product['productNo'];
				$v['top'] = $k+1;
			}
			$num = $this->db->result_first("SELECT COUNT(*) as num FROM god_report_basis WHERE reportId IN(SELECT reportId FROM god_report_basis $sqladd GROUP BY productId)");
			//echo "SELECT COUNT(*) as num FROM god_report_basis $sqladd GROUP BY productId";
			$multipage = page($num, $ppp, $page, "report-product.htm?startDate=$startDate&endDate=$endDate&province=$province&city=$city");
			
			$cityList = $this->AreaModel->findAll("fId='$province'", 0, 0, "id ASC");
			
			$this->view->assign('reportList', $reportList);
			$this->view->assign('startDate', $startDate);
			$this->view->assign('endDate', $endDate);
			$this->view->assign('province', $province);
			$this->view->assign('cityList', $cityList);
			$this->view->assign('city', $city);
			$this->view->assign('multipage', $multipage);
		}
		
		$this->view->assign('provinceList', $provinceList);
		$this->display('report_product');
	}
	
	
	
	public function actionFenlei() {
		//产品系列分类
		$provinceList = $this->AreaModel->findAll("fId='1'", 0, 0, "id ASC");
		
		$page = getgpc('page');
		
		if(isset($_POST['submitTo']) || is_numeric($page)) {
			$sqladd = '';
			$startDate = getgpc('startDate', 'R');
			$endDate = getgpc('endDate', 'R');
			if($startDate && $endDate) {
				$startDate = str_replace('-', '', $startDate);
				$endDate = str_replace('-', '', $endDate);
				$sqladd .= " (reportDate>='$startDate' AND reportDate<='$endDate') AND";
			}
			
			$categoryId = getgpc('categoryId', 'R');
			$categoryId2 = getgpc('categoryId2', 'R');
			
			
			if($categoryId2=='0'){
				$sqladd .= " upperparentId='$categoryId' AND";
			}else{
				$sqladd .= " upperparentId='$categoryId' AND parentId='$categoryId2' AND";
			}
			
			if($sqladd) {
				$sqladd = "WHERE ".substr($sqladd, 0, -3);
			}
			
			$page = max(1, $page);
			$ppp = GODHOUSE_PPP3;
			$start = ($page-1)*$ppp;

			// 产品销量排行
			if($categoryId2=='0'){
			$sql = "SELECT SUM(cartCount) as num, productId FROM god_report_basis $sqladd GROUP BY upperparentId ORDER BY num DESC LIMIT $start, $ppp";
			}else{
			$sql = "SELECT SUM(cartCount) as num, productId FROM god_report_basis $sqladd GROUP BY parentId ORDER BY num DESC LIMIT $start, $ppp";
			}
			//echo $sql;
			$reportList = $this->db->fetch_all($sql);
			foreach($reportList as $k=>&$v) {
				$product = $this->ProductModel->findByPk($v['productId']);
				$category = $this->CategoryModel->findByPk($product['categoryId']);
				$v['cateName'] = $category['cateName'];
				$v['content'] = $product['content'];
				$v['productNo'] = $product['productNo'];
				$v['top'] = $k+1;
			}
			$num = $this->db->result_first("SELECT COUNT(*) as num FROM god_report_basis WHERE reportId IN(SELECT reportId FROM god_report_basis $sqladd GROUP BY productId)");
			//echo "SELECT COUNT(*) as num FROM god_report_basis $sqladd GROUP BY productId";
			$multipage = page($num, $ppp, $page, "report-product.htm?startDate=$startDate&endDate=$endDate&categoryId=$categoryId&parentId=$categoryId");
			
			$cityList = $this->AreaModel->findAll("fId='$province'", 0, 0, "id ASC");
			
			$this->view->assign('reportList', $reportList);
			$this->view->assign('startDate', $startDate);
			$this->view->assign('endDate', $endDate);
			$this->view->assign('province', $province);
			$this->view->assign('cityList', $cityList);
			$this->view->assign('city', $city);
			$this->view->assign('multipage', $multipage);
		}
		
		
		$optionListArr = array();
		$listArr = $this->CategorysPublic->get_list(GODHOUSE_CATEGORY_PRODUCT, 1);
		
		foreach($listArr as $key => $val) {
			$optionListArr[] = array('categoryId' => $val['categoryId'], 'cateName' => str_repeat('　', $val['level']) . '├ ' . $val['cateName']);
		}
		unset($listArr);
		//print_r($optionListArr);
		$this->view->assign('optionListArr', $optionListArr);
		
		
		$optionListArr2 = array();
		$listArr = $this->CategorysPublic->get_list(GODHOUSE_CATEGORY_PRODUCT, 2);
		
		foreach($listArr as $key => $val) {
			$optionListArr2[] = array('categoryId' => $val['categoryId'], 'cateName' => str_repeat('　', $val['level']) . '├ ' . $val['cateName']);
		}
		unset($listArr);
		//print_r($optionListArr);
		$this->view->assign('optionListArr2', $optionListArr2);
		
		
		$this->view->assign('provinceList', $provinceList);
		$this->display('report_fenlei');
	}
	
	
	
		public function actionPrice() {
		//分类总金额报表
		$provinceList = $this->AreaModel->findAll("fId='1'", 0, 0, "id ASC");
		
		$page = getgpc('page');
		
		if(isset($_POST['submitTo']) || is_numeric($page)) {
			$sqladd = '';
			$startDate = getgpc('startDate', 'R');
			$endDate = getgpc('endDate', 'R');
			if($startDate && $endDate) {
				$startDate = str_replace('-', '', $startDate);
				$endDate = str_replace('-', '', $endDate);
				$sqladd .= " (reportDate>='$startDate' AND reportDate<='$endDate') AND";
			}
			
			$categoryId = getgpc('categoryId', 'R');
			$categoryId2 = getgpc('categoryId2', 'R');
			
			
			
			if($sqladd) {
				$sqladd = "WHERE ".substr($sqladd, 0, -3);
			}
			
			$page = max(1, $page);
			$ppp = GODHOUSE_PPP3;
			$start = ($page-1)*$ppp;

			// 产品销量排行
			
			$sql = "SELECT sum(cartCount*ourPrice*discount) as price,productId FROM `god_shoppingcart`   group by productId  order by price desc LIMIT $start, $ppp";
			
			//echo $sql;
			$reportList = $this->db->fetch_all($sql);
			
			foreach($reportList as $k=>&$v) {
				$product = $this->ProductModel->findByPk($v['productId']);
				$category = $this->CategoryModel->findByPk($product['upperparentId']);
				$v['cateName'] = $category['cateName'];
				$categorys = $this->CategoryModel->findByPk($product['parentId']);
				$v['cateNames'] = $categorys['cateName'];
				$v['productId'] = $product['productNo'];
				$v['price'] = number_format($v['price'], 2, '.', '');

				$v['top'] = $k+1;
			}
			
			$num = $this->db->result_first("SELECT COUNT(*) as num FROM god_report_basis WHERE reportId IN(SELECT reportId FROM god_report_basis $sqladd GROUP BY productId)");
			//echo "SELECT COUNT(*) as num FROM god_report_basis $sqladd GROUP BY productId";
			$multipage = page($num, $ppp, $page, "report-price.htm?startDate=$startDate&endDate=$endDate");
			
			$cityList = $this->AreaModel->findAll("fId='$province'", 0, 0, "id ASC");
			
			$this->view->assign('reportList', $reportList);
			$this->view->assign('startDate', $startDate);
			$this->view->assign('endDate', $endDate);
			$this->view->assign('province', $province);
			$this->view->assign('cityList', $cityList);
			$this->view->assign('city', $city);
			$this->view->assign('multipage', $multipage);
		}
		
		
		$optionListArr = array();
		$listArr = $this->CategorysPublic->get_list(GODHOUSE_CATEGORY_PRODUCT, 1);
		
		foreach($listArr as $key => $val) {
			$optionListArr[] = array('categoryId' => $val['categoryId'], 'cateName' => str_repeat('　', $val['level']) . '├ ' . $val['cateName']);
		}
		unset($listArr);
		//print_r($optionListArr);
		$this->view->assign('optionListArr', $optionListArr);
		
		
		$optionListArr2 = array();
		$listArr = $this->CategorysPublic->get_list(GODHOUSE_CATEGORY_PRODUCT, 2);
		
		foreach($listArr as $key => $val) {
			$optionListArr2[] = array('categoryId' => $val['categoryId'], 'cateName' => str_repeat('　', $val['level']) . '├ ' . $val['cateName']);
		}
		unset($listArr);
		//print_r($optionListArr);
		$this->view->assign('optionListArr2', $optionListArr2);
		
		
		$this->view->assign('provinceList', $provinceList);
		$this->display('report_price');
	}
	
	/* 产品地区分布 */ 
	public function actionProductAreaDistribution() {
		$productId = getgpc(2);
		
		$sqladd = '';
		$startDate = getgpc('startDate', 'R');
		$endDate = getgpc('endDate', 'R');
		if($startDate && $endDate) {
			$startDate = str_replace('-', '', $startDate);
			$endDate = str_replace('-', '', $endDate);
			$sqladd .= " AND (reportDate>='$startDate' AND reportDate<='$endDate')";
		}
		
		$sqladd = "SELECT SUM(cartCount) as num,city FROM god_report_basis WHERE productId='$productId' $sqladd GROUP BY city ORDER BY num DESC";
		$reportList = $this->db->fetch_all($sqladd);
		$totalRatio = 0;
		foreach($reportList as $k=>&$v) {
			$v['top'] = $k+1;
			
			$area = $this->AreaModel->findByPk($v['city']);
			$v['cityName'] = $area['name'];
			$totalRatio = $totalRatio + $v['num'];
		}
		foreach($reportList as &$v) {
			$v['ratio'] = round($v['num']/$totalRatio*100);
		}
		
		$product = $this->ProductModel->findByPk($productId);
		
		$this->view->assign('product', $product);
		$this->view->assign('reportList', $reportList);
		$this->display('report_product_area_distribution');
	}
	
	public function actionUser() {
		// 客户分析系统：按时间段，分析客户采购产品的类别、价位、颜色等数据。
		$reportType = getgpc(2);
		
		$page = getgpc('page');
		
		if(isset($_REQUEST['submitTo'])) {
			$sqladd = '';
			$startDate = getgpc('startDate', 'R');
			$endDate = getgpc('endDate', 'R');
			$submitTo = getgpc('submitTo', 'R');
			//$reportType= getgpc('reportType', 'G');
			//echo $reportType;
			if($startDate && $endDate) {
				$startDates = str_replace('-', '', $startDate);
				$endDates = str_replace('-', '', $endDate);
				$sqladd = "WHERE reportDate>='$startDates' AND reportDate<='$endDates'";
			}
			
			
			$page = max(1, $page);
			$ppp = GODHOUSE_PPP3;
			$start = ($page-1)*$ppp;
			
if (!$sqladd){
$sql = "SELECT SUM(price) as price,userId,city FROM (select distinct orderID,price,userId,city FROM god_report_basis )  god_report_basis   GROUP BY userId ORDER BY price DESC LIMIT $start, $ppp";
}else{
$sql = "SELECT SUM(price) as price,userId,reportDate,city FROM (select distinct orderID,price,userId,reportDate,city FROM god_report_basis )  god_report_basis  $sqladd GROUP BY userId ORDER BY price DESC LIMIT $start, $ppp";
}
			//$sql = "SELECT SUM(price),userId FROM (select distinct orderID,price,userId FROM god_report_basis) god_report_basis GROUP BY userId ";
			$reportList = $this->db->fetch_all($sql);
			$totalRatio = 0;
			foreach($reportList as $k=>&$v) {
				$v['top'] = $k+1;
				$num = $this->db->result_first("SELECT COUNT(*) as num FROM god_report_basis WHERE userId = '".$v['userId']."' ");
				$v['num']=$num;
				$prod = $this->db->result_first("SELECT SUM(cartCount) as prod FROM god_report_basis WHERE userId = '".$v['userId']."' ");
				$v['prod']=$prod;
				$user = $this->UserModel->findByPk($v['userId']);
				$v['userName'] = $user['userName'];
				$v['realName'] = $user['realName'];
				
				if (!$sqladd){		
		$sqls = "SELECT SUM(price),userId FROM (select distinct orderID,price,userId FROM god_report_basis )  god_report_basis WHERE userId = '".$v['userId']."' ";
				}else{
		$sqls = "SELECT SUM(price),userId,reportDate FROM (select distinct orderID,price,userId,reportDate FROM god_report_basis )  god_report_basis $sqladd AND userId = '".$v['userId']."' ";
				}		
				$res = $this->db->result_first($sqls);
				$v['totalPrice'] = $res;

				$area = $this->AreaModel->findByPk($v['city']);
				$v['cityName'] = $area['name'];
				$totalRatio = $totalRatio + $v['num'];
			}
			foreach($reportList as &$v) {
				$v['ratio'] = round($v['num']/$totalRatio*100);
			}
			//echo $sql;
			//print_r($reportList);
			$num = $this->db->result_first("SELECT COUNT(*) as num FROM god_report_basis WHERE reportId IN(SELECT reportId FROM god_report_basis $sqladd GROUP BY userId)");
			//$num = $this->ReportBasisModel->count(substr($sqladd, 5));
			$multipage = page($num, $ppp, $page, "report-user-$reportType.htm?submitTo=$submitTo&reportType=$reportType&startDate=$startDate&endDate=$endDate");
			
			$this->view->assign('reportList', $reportList);
			$this->view->assign('startDate', $startDate);
			$this->view->assign('num', $reportType);
			$this->view->assign('endDate', $endDate);
			$this->view->assign('multipage', $multipage);
		}
		
		$this->view->assign('reportType', $reportType);
		$this->display('report_user');
	}
	
	
	
	public function actionMon() {
		// 客户分析系统:月份总额报表。
			$page = max(1, getgpc('page'));
		if(isset($_REQUEST['submitTo'])) {
			$sqladd="";
			$startDate = getgpc('startDate', 'R');
			$endDate = getgpc('endDate', 'R');
			$submitTo = getgpc('submitTo', 'R');
			$reportType = getgpc(2);
			if($startDate && $endDate) {
				$startDates = strtotime($startDate);
				$endDates = strtotime($endDate);
				$sqladd = "orderStatus != '3' AND createdTime >= '$startDates' AND createdTime <= '$endDates'  ";
			}
			
			
			$reportList = $this->OrderModel->findAll($sqladd, $page, GODHOUSE_PPP3);
				foreach($reportList as $k=>&$v) {
				$v['top'] = $k+1;
				$user = $this->UserModel->findByPk($v['userId']);
				$v['userName'] = $user['userName'];
				$v['realName'] = $user['realName'];
				$v['company'] = $user['company'];
				$v['createdTime'] = date('Y-m-d',$v['createdTime']);
				
				if ($sqladd){
				$nums = $this->db->result_first("SELECT SUM(totalPrice) as total FROM god_order WHERE $sqladd   ");
				}else{
				$nums = $this->db->result_first("SELECT SUM(totalPrice) as total FROM god_order $sqladd   ");
				}
				$v['total'] = $nums;
			}
			$num = $this->OrderModel->count($sqladd);
			$multipage = page($num, GODHOUSE_PPP3, $page, "report-mon-$reportType.htm?submitTo=$submitTo&startDate=$startDate&endDate=$endDate");

			$this->view->assign('reportList', $reportList);
			$this->view->assign('startDate', $startDate);
			$this->view->assign('endDate', $endDate);
			$this->view->assign('multipage', $multipage);
	}
		
		$this->view->assign('reportType', $reportType);
		$this->display('report_mon');
	}
	
	

	public function actionQiy() {
		// 客户分析系统:企业订单月份总额报表。
			$page = max(1, getgpc('page'));
		if(isset($_REQUEST['submitTo'])) {
			$sqladd="";
			$startDate = getgpc('startDate', 'R');
			$endDate = getgpc('endDate', 'R');
			$submitTo = getgpc('submitTo', 'R');
			$reportType = getgpc(2);
			if($startDate && $endDate) {
				
				$sqladd = "times >= '$startDate' AND times <= '$endDate'  ";
			}
			$reportList = $this->QiyeModel->findAll($sqladd, $page, GODHOUSE_PPP3);
				foreach($reportList as $k=>&$v) {
				$v['top'] = $k+1;
				
				
				if ($sqladd){
				$nums = $this->db->result_first("SELECT SUM(total) as totals FROM god_qiye WHERE $sqladd   ");
				}else{
				$nums = $this->db->result_first("SELECT SUM(total) as totals FROM god_qiye $sqladd   ");
				}
				$v['totals'] = $nums;
			}
			$num = $this->QiyeModel->count($sqladd);
			$multipage = page($num, GODHOUSE_PPP3, $page, "report-qiy-$reportType.htm?submitTo=$submitTo&startDate=$startDate&endDate=$endDate");

			$this->view->assign('reportList', $reportList);
			$this->view->assign('startDate', $startDate);
			$this->view->assign('endDate', $endDate);
			$this->view->assign('multipage', $multipage);
	}
		
		$this->view->assign('reportType', $reportType);
		$this->display('report_qiy');
	}
	
	
	
	/* 地区 */
	public function actionAjaxCity() {
		$fId = getgpc(2);
		
		$areaList = $this->AreaModel->findAll("fId='$fId'");
		
		$str = '<select name="city" id="city" style="width:120px;">';
		foreach($areaList as $v) {
			$str .= '<option value="'.$v['id'].'">'.$v['name'].'</option>';
		}
		$str .= '</select>';
		$this->message($str);
	}
	
}